﻿/*=================================================================
* 版权信息：Copyright (C) 2005-2011 捷点科技 All Rights Reserved.
* 文件名称：CNVP_Users.cs
* 创建标识：曾乃强 15158756652 2011-03-17 11:03:01
* 修改标识：
* 功能描述：
===================================================================*/
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using CNVP.Framework;
using CNVP.Config;
using CNVP.Framework.Helper;

namespace CNVP.JShop.DAL
{
    /// <summary>
    /// 对*CNVP_Users*操作数据库(针对SQL Server)的实现
    /// </summary>
    public class CNVP_Users
    {
        private string Prefix = DBConfig.Prefix;
        #region DAL
        public int Insert(Model.CNVP_Users obj)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("insert into  " + Prefix + "Users ([UserName],[UserPass],[UserLevelID],[Email],[PointsNum],[TotalAmount],[AddTime],[AddIP],[EndLoginIP],[EndLoginTime],[OtherText],[IsDelete],[LoginNum],[MobilePhone], [Province], [City], [Area], [GetAddress])");
            sql.Append("values(@UserName,@UserPass,@UserLevelID,@Email,@PointsNum,@TotalAmount,@AddTime,@AddIP,@EndLoginIP,@EndLoginTime,@OtherText,@IsDelete,@LoginNum,@MobilePhone, @Province, @City, @Area, @GetAddress)");
            
            SqlParameter[] pra = {
                            new SqlParameter("@UserName",obj.UserName),
							new SqlParameter("@UserPass",obj.UserPass),
							new SqlParameter("@UserLevelID",obj.UserLevelID),
							new SqlParameter("@Email",obj.Email),
							new SqlParameter("@PointsNum",obj.PointsNum),
							new SqlParameter("@TotalAmount",obj.TotalAmount),
							new SqlParameter("@AddTime",obj.AddTime),
							new SqlParameter("@AddIP",obj.AddIP),
							new SqlParameter("@EndLoginIP",obj.EndLoginIP),
							new SqlParameter("@EndLoginTime",obj.EndLoginTime),
							new SqlParameter("@OtherText",obj.OtherText),
							new SqlParameter("@IsDelete",obj.IsDelete),
                            new SqlParameter("@LoginNum",obj.LoginNum),
                            new SqlParameter("@MobilePhone",obj.MobilePhone),
							new SqlParameter("@Province",obj.Province),
							new SqlParameter("@City",obj.City),
							new SqlParameter("@Area",obj.Area),
                            new SqlParameter("@GetAddress",obj.GetAddress)
                                 };
            return DbHelper.ExecuteNonQuery(sql.ToString(), pra);
        }

        public int Update(Model.CNVP_Users obj)
        {
             StringBuilder sql = new StringBuilder();
             sql.Append("update " + Prefix + "Users set [UserName] = @UserName,[UserLevelID] = @UserLevelID,[Email] = @Email,[EndLoginIP] = @EndLoginIP,[EndLoginTime] = @EndLoginTime,[OtherText] = @OtherText,[MobilePhone]=@MobilePhone,[Province]=@Province,[City]=@City,[Area]=@Area,[GetAddress]=@GetAddress where [ID] = @ID");

            SqlParameter[] pra = { 
                            new SqlParameter("@ID",obj.ID),
							new SqlParameter("@UserName",obj.UserName),
							new SqlParameter("@UserLevelID",obj.UserLevelID),
							new SqlParameter("@Email",obj.Email),
							new SqlParameter("@EndLoginIP",obj.EndLoginIP),
							new SqlParameter("@EndLoginTime",obj.EndLoginTime),
							new SqlParameter("@OtherText",obj.OtherText),
                            new SqlParameter("@MobilePhone",obj.MobilePhone),
							new SqlParameter("@Province",obj.Province),
							new SqlParameter("@City",obj.City),
							new SqlParameter("@Area",obj.Area),
                            new SqlParameter("@GetAddress",obj.GetAddress)
                                };
             return DbHelper.ExecuteNonQuery(sql.ToString(),pra);
        }

        public int Delete(Model.CNVP_Users obj)
        {
           StringBuilder sql = new StringBuilder();
           sql.Append("delete from " + Prefix + "Users where [ID] = @ID");
           SqlParameter[] pra = {
                                new SqlParameter("@ID",obj.ID)
						
                                };
           return DbHelper.ExecuteNonQuery(sql.ToString(), pra);
        }

        public List<Model.CNVP_Users> LoadAll(string sqlWhere)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select [ID],[UserName],[UserPass],[UserLevelID],[Email],[PointsNum],[TotalAmount],[AddTime],[AddIP],[EndLoginIP],[EndLoginTime],[OtherText],[IsDelete],[LoginNum] from " + Prefix + "Users");
            if (sqlWhere.Trim() != "")
            {
                sql.Append(" where " + sqlWhere);
            }
            DataTable DT = DbHelper.ExecuteTable(sql.ToString());
            List<Model.CNVP_Users> list=new List<Model.CNVP_Users>();
            if (DT != null && DT.Rows.Count > 0)
            {
                foreach (DataRow dr in DT.Rows)
                {
                    Model.CNVP_Users cnvp_users = new Model.CNVP_Users();
                    cnvp_users.ID = dr["ID"] is DBNull ? 0 : (int)dr["ID"];
                    cnvp_users.UserName = dr["UserName"] is DBNull ? String.Empty : (string)dr["UserName"];
                    cnvp_users.UserPass = dr["UserPass"] is DBNull ? String.Empty : (string)dr["UserPass"];
                    cnvp_users.UserLevelID = dr["UserLevelID"] is DBNull ? 0 : (int)dr["UserLevelID"];
                    cnvp_users.Email = dr["Email"] is DBNull ? String.Empty : (string)dr["Email"];
                    cnvp_users.PointsNum = dr["PointsNum"] is DBNull ? 0 : (int)dr["PointsNum"];
                    cnvp_users.TotalAmount = dr["TotalAmount"] is DBNull ? (decimal)0.00 : (decimal)dr["TotalAmount"];
                    cnvp_users.AddTime = dr["AddTime"] is DBNull ? DateTime.Now : (DateTime)dr["AddTime"];
                    cnvp_users.AddIP = dr["AddIP"] is DBNull ? String.Empty : (string)dr["AddIP"];
                    cnvp_users.EndLoginIP = dr["EndLoginIP"] is DBNull ? String.Empty : (string)dr["EndLoginIP"];
                    cnvp_users.EndLoginTime = dr["EndLoginTime"] is DBNull ? DateTime.Now : (DateTime)dr["EndLoginTime"];
                    cnvp_users.OtherText = dr["OtherText"] is DBNull ? String.Empty : (string)dr["OtherText"];
                    cnvp_users.IsDelete = dr["IsDelete"] is DBNull ? (byte)0 : (byte)dr["IsDelete"];
                    cnvp_users.LoginNum = dr["LoginNum"] is DBNull ? 0 : (int)dr["LoginNum"];
                    list.Add(cnvp_users);
                }
            }
            return list;

        }

        public Model.CNVP_Users LoadById(int ID)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select [UserName],[UserPass],[UserLevelID],[Email],[PointsNum],[TotalAmount],[AddTime],[AddIP],[EndLoginIP],[EndLoginTime],[OtherText],[IsDelete],[LoginNum],[MobilePhone],[Province],[City],[Area],[GetAddress] from " + Prefix + "Users");
            sql.Append(" where [ID] = @ID");
            SqlParameter[] pra = {
                                 new SqlParameter("@ID",ID)
                                    };
            DataTable DT = DbHelper.ExecuteTable(sql.ToString(),pra);
            Model.CNVP_Users cnvp_users = new Model.CNVP_Users();
            if (DT != null && DT.Rows.Count > 0)
            {
                DataRow dr = DT.Rows[0];
                cnvp_users.UserName = dr["UserName"] is DBNull ? String.Empty : (string)dr["UserName"];
                cnvp_users.UserPass = dr["UserPass"] is DBNull ? String.Empty : (string)dr["UserPass"];
                cnvp_users.UserLevelID = dr["UserLevelID"] is DBNull ? 0 : (int)dr["UserLevelID"];
                cnvp_users.Email = dr["Email"] is DBNull ? String.Empty : (string)dr["Email"];
                cnvp_users.PointsNum = dr["PointsNum"] is DBNull ? 0 : (int)dr["PointsNum"];
                cnvp_users.TotalAmount = dr["TotalAmount"] is DBNull ? (decimal)0.00 : (decimal)dr["TotalAmount"];
                cnvp_users.AddTime = dr["AddTime"] is DBNull ? DateTime.Now : (DateTime)dr["AddTime"];
                cnvp_users.AddIP = dr["AddIP"] is DBNull ? String.Empty : (string)dr["AddIP"];
                cnvp_users.EndLoginIP = dr["EndLoginIP"] is DBNull ? String.Empty : (string)dr["EndLoginIP"];
                cnvp_users.EndLoginTime = dr["EndLoginTime"] is DBNull ? DateTime.Now : (DateTime)dr["EndLoginTime"];
                cnvp_users.OtherText = dr["OtherText"] is DBNull ? String.Empty : (string)dr["OtherText"];
                cnvp_users.IsDelete = dr["IsDelete"] is DBNull ? (byte)0 : (byte)dr["IsDelete"];
                cnvp_users.LoginNum = dr["LoginNum"] is DBNull ? 0 : (int)dr["LoginNum"];
                cnvp_users.MobilePhone = dr["MobilePhone"] is DBNull ? String.Empty : (string)dr["MobilePhone"];
                cnvp_users.Province = dr["Province"] is DBNull ? String.Empty : (string)dr["Province"];
                cnvp_users.City = dr["City"] is DBNull ? String.Empty : (string)dr["City"];
                cnvp_users.Area = dr["Area"] is DBNull ? String.Empty : (string)dr["Area"];
                cnvp_users.GetAddress = dr["GetAddress"] is DBNull ? String.Empty : (string)dr["GetAddress"];
            }
            return cnvp_users;
        }

        #endregion

        public DataTable GetPage(string PageName, int PageIndex, int PageSize, out int RecordCount, out int PageCount, params SQLConditionInfo[] SqlCondition)
        {
            try
            {
                 string sql_Temp = "";
                 if (SqlCondition[3].value.ToString() == "0")
                 {
                     if (SqlCondition[1].value.ToString() != "")
                     {
                         sql_Temp += " and UserName like '%" + SqlCondition[1].value.ToString() + "%' or  Email like '%" + SqlCondition[1].value.ToString() + "%'";
                     }
                 }
                 else
                 {
                     if (SqlCondition[0].value.ToString() != "")
                     {
                         sql_Temp += " and UserLevelID=" + SqlCondition[0].value.ToString() + "";
                     }
                     if (SqlCondition[2].value.ToString() != "")
                     {
                         sql_Temp += " and UserName like '%" + SqlCondition[2].value.ToString() + "%' or  Email like '%" + SqlCondition[2].value.ToString() + "%'";
                     }
                }
                 string AllFields = " ID, UserName, UserPass, UserLevelID, Email, PointsNum, TotalAmount,AddTime,LoginNum";
                string Condition = " " + Prefix + "Users where 1=1" + sql_Temp + "";
                string IndexField = "ID";
                string OrderFields = "order by Id Desc";
                return DbHelper.ExecutePage(AllFields, Condition, IndexField, OrderFields, PageIndex, PageSize, out RecordCount, out PageCount, null);
            }
            catch (Exception e)
            {
                throw new Exception("用于分页的SQL语句无效:" + e.Message);
            }

        }

        /// <summary>
        /// 更新积分
        /// </summary>
        /// <param name="obj"></param>
        /// <param name="pointsNum">当前更新的积分</param>
        /// <returns></returns>
        public int UpdatePointsNum(int ID, int PointsNum)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("update " + Prefix + "Users set [PointsNum] = @PointsNum where [ID] = @ID");

            SqlParameter[] pra = { 
                            new SqlParameter("@ID",ID),
                            new SqlParameter("@pointsNum",PointsNum)
                                };
            return DbHelper.ExecuteNonQuery(sql.ToString(), pra);
        }

        /// <summary>
        /// 更新积分
        /// </summary>
        /// <param name="obj"></param>
        /// <param name="pointsNum">当前更新的积分</param>
        /// <returns></returns>
        public int UpdatePointsNumType(int ID, int PointsNum,int type)
        {
            StringBuilder sql = new StringBuilder();
            if (type == 0)
            {
                sql.Append("update " + Prefix + "Users set [PointsNum] =[PointsNum]- @PointsNum where [ID] = @ID");
            }
            else
            {
                sql.Append("update " + Prefix + "Users set [PointsNum] =[PointsNum]+ @PointsNum where [ID] = @ID");
            }
            SqlParameter[] pra = { 
                            new SqlParameter("@ID",ID),
                            new SqlParameter("@pointsNum",PointsNum)
                                };
            return DbHelper.ExecuteNonQuery(sql.ToString(), pra);
        }

        public List<Model.CNVP_Users> QurryListByUserName(string UserName)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select [ID],[UserPass],[UserLevelID],[Email],[PointsNum],[TotalAmount],[AddTime],[AddIP],[EndLoginIP],[EndLoginTime],[OtherText],[IsDelete] from " + Prefix + "Users where [UserName]=@UserName");
            SqlParameter[] pra = { 
                            new SqlParameter("@UserName",UserName),
                                };
            DataTable DT = DbHelper.ExecuteTable(sql.ToString(),pra);
            List<Model.CNVP_Users> list = new List<Model.CNVP_Users>();
            if (DT != null && DT.Rows.Count > 0)
            {
                foreach (DataRow dr in DT.Rows)
                {
                    Model.CNVP_Users cnvp_users = new Model.CNVP_Users();
                    cnvp_users.ID = dr["ID"] is DBNull ? 0 : (int)dr["ID"];
                    cnvp_users.UserPass = dr["UserPass"] is DBNull ? String.Empty : (string)dr["UserPass"];
                    cnvp_users.UserLevelID = dr["UserLevelID"] is DBNull ? 0 : (int)dr["UserLevelID"];
                    cnvp_users.Email = dr["Email"] is DBNull ? String.Empty : (string)dr["Email"];
                    cnvp_users.PointsNum = dr["PointsNum"] is DBNull ? 0 : (int)dr["PointsNum"];
                    cnvp_users.TotalAmount = dr["TotalAmount"] is DBNull ? (decimal)0.00 : (decimal)dr["TotalAmount"];
                    cnvp_users.AddTime = dr["AddTime"] is DBNull ? DateTime.Now : (DateTime)dr["AddTime"];
                    cnvp_users.AddIP = dr["AddIP"] is DBNull ? String.Empty : (string)dr["AddIP"];
                    cnvp_users.EndLoginIP = dr["EndLoginIP"] is DBNull ? String.Empty : (string)dr["EndLoginIP"];
                    cnvp_users.EndLoginTime = dr["EndLoginTime"] is DBNull ? DateTime.Now : (DateTime)dr["EndLoginTime"];
                    cnvp_users.OtherText = dr["OtherText"] is DBNull ? String.Empty : (string)dr["OtherText"];
                    cnvp_users.IsDelete = dr["IsDelete"] is DBNull ? (byte)0 : (byte)dr["IsDelete"];
                    list.Add(cnvp_users);
                }
            }
            return list;
        }

        /// <summary>
        /// 用户登录
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public Model.CNVP_Users cust_LoginByName(Model.CNVP_Users obj)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select ID,UserLevelID from " + Prefix + "Users");
            sql.Append(" where [UserName]=@UserName AND [UserPass]=@UserPass AND [IsDelete]=@IsDelete");
            SqlParameter[] pra = { 
							new SqlParameter("@UserName",obj.UserName),
							new SqlParameter("@UserPass",obj.UserPass),
                            new SqlParameter("@IsDelete",obj.IsDelete)
                                  };
            DataTable dt = DbHelper.ExecuteTable(sql.ToString(), pra);
            if (dt != null && dt.Rows.Count > 0)
            {
                obj.ID = Convert.ToInt32(dt.Rows[0]["ID"]);
                obj.UserLevelID = Convert.ToInt32(dt.Rows[0]["UserLevelID"]);
                return obj;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 找回密码
        /// </summary>
        /// <param name="UserName"></param>
        /// <param name="Email"></param>
        /// <returns></returns>
        public Model.CNVP_Users LoadByUserName(string UserName,string Email)
        {

            StringBuilder sql = new StringBuilder();
            sql.Append("select [ID],[UserPass],[UserLevelID],[Email],[PointsNum],[TotalAmount],[AddTime],[AddIP],[EndLoginIP],[EndLoginTime],[OtherText],[IsDelete],[LoginNum] from " + Prefix + "Users");
            sql.Append(" where [UserName] = @UserName and Email = @Email");
            SqlParameter[] pra = {
                                 new SqlParameter("@UserName",UserName),
                                 new SqlParameter("@Email",Email)
						
                                    };
            DataTable DT = DbHelper.ExecuteTable(sql.ToString(), pra);
            Model.CNVP_Users cnvp_users = new Model.CNVP_Users();
            if (DT != null && DT.Rows.Count > 0)
            {
                DataRow dr = DT.Rows[0];
                cnvp_users.ID = dr["ID"] is DBNull ? 0 : (int)dr["ID"];
                cnvp_users.UserPass = dr["UserPass"] is DBNull ? String.Empty : (string)dr["UserPass"];
                cnvp_users.UserLevelID = dr["UserLevelID"] is DBNull ? 0 : (int)dr["UserLevelID"];
                cnvp_users.PointsNum = dr["PointsNum"] is DBNull ? 0 : (int)dr["PointsNum"];
                cnvp_users.TotalAmount = dr["TotalAmount"] is DBNull ? (decimal)0.00 : (decimal)dr["TotalAmount"];
                cnvp_users.AddTime = dr["AddTime"] is DBNull ? DateTime.Now : (DateTime)dr["AddTime"];
                cnvp_users.AddIP = dr["AddIP"] is DBNull ? String.Empty : (string)dr["AddIP"];
                cnvp_users.EndLoginIP = dr["EndLoginIP"] is DBNull ? String.Empty : (string)dr["EndLoginIP"];
                cnvp_users.EndLoginTime = dr["EndLoginTime"] is DBNull ? DateTime.Now : (DateTime)dr["EndLoginTime"];
                cnvp_users.OtherText = dr["OtherText"] is DBNull ? String.Empty : (string)dr["OtherText"];
                cnvp_users.IsDelete = dr["IsDelete"] is DBNull ? (byte)0 : (byte)dr["IsDelete"];
                cnvp_users.LoginNum = dr["LoginNum"] is DBNull ? 0 : (int)dr["LoginNum"];
                
            }
            return cnvp_users;
        }

        /// <summary>
        /// 判断是否存在旧密码
        /// </summary>
        public int IsOldPwd(string OldPwd, string UserName)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select Count([ID]) from " + Prefix + "Users");
            sql.Append(" Where [UserPass]=@OldPwd and UserName = @UserName");
            SqlParameter[] pra = {
                                 new SqlParameter("@OldPwd",OldPwd),
                                  new SqlParameter("@UserName",UserName)
                                    };
            return Convert.ToInt32(DbHelper.ExecuteScalar(sql.ToString(), pra));
        }

        /// <summary>
        /// 旧密码存在，更新密码
        /// </summary>
        public int UpdatePwd(string UserPass, string UserName)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("Update " + Prefix + "Users set [UserPass] = @UserPass");
            sql.Append(" where  UserName = @UserName");
            SqlParameter[] pra = {
                                 new SqlParameter("@UserPass",UserPass),
                                  new SqlParameter("@UserName",UserName)
                                    };
            return Convert.ToInt32(DbHelper.ExecuteScalar(sql.ToString(), pra));
        }

        /// <summary>
        /// 会员中心资料管理
        /// </summary>
        /// <param name="obj"></param>
        /// <param name="UserName"></param>
        /// <returns></returns>
        public int UpdateByUserName(Model.CNVP_Users obj)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("update " + Prefix + "Users set [Email] = @Email,[OtherText] = @OtherText,[MobilePhone]=@MobilePhone,[Province]=@Province,[City]=@City,[Area]=@Area,[GetAddress]=@GetAddress where [UserName] = @UserName");

            SqlParameter[] pra = { 
                            new SqlParameter("@UserName",obj.UserName),
                            new SqlParameter("@Email",obj.Email),
                            new SqlParameter("@OtherText",obj.OtherText),
                            new SqlParameter("@MobilePhone",obj.MobilePhone),
							new SqlParameter("@Province",obj.Province),
							new SqlParameter("@City",obj.City),
							new SqlParameter("@Area",obj.Area),
                            new SqlParameter("@GetAddress",obj.GetAddress)
                                };
            return DbHelper.ExecuteNonQuery(sql.ToString(), pra);
        }

        public int UpdateUserLevelID(int ID, int UserLevelID)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("update " + Prefix + "Users set [UserLevelID] = @UserLevelID where [ID] = @ID");

            SqlParameter[] pra = { 
                            new SqlParameter("@ID",ID),
                            new SqlParameter("@UserLevelID",UserLevelID)
                                };
            return DbHelper.ExecuteNonQuery(sql.ToString(), pra);
        }

        public int cust_UpdateLogin(Model.CNVP_Users obj)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("update " + Prefix + "Users set [EndLoginIP] = @EndLoginIP,[EndLoginTime] = @EndLoginTime,[LoginNum] = [LoginNum]+1 where [UserName]=@UserName AND [UserPass]=@UserPass AND [IsDelete]=@IsDelete");

            SqlParameter[] pra = { 
                            new SqlParameter("@UserName",obj.UserName),
							new SqlParameter("@UserPass",obj.UserPass),
                            new SqlParameter("@EndLoginIP",obj.EndLoginIP),
							new SqlParameter("@EndLoginTime",obj.EndLoginTime),
                            new SqlParameter("@IsDelete",obj.IsDelete)
                                };
            return DbHelper.ExecuteNonQuery(sql.ToString(), pra);
        }

    }
}

